﻿using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Vroom.LayerTranslator
{
	public class LayerTranslator
	{
		#region props

		private Server SmoSqlServer { get; set; }
		private string DatabaseHost { get; set; }
		private string DatabaseName { get; set; }
		private string SchemaFrom { get; set; }
		private string SchemaTo { get; set; }
		private string TablePattern { get; set; }
		private string ConnString { get; set; }

		#endregion

		public static void Main(string[] args)
		{
			LayerTranslator layerTranslator = new LayerTranslator();
			layerTranslator.Init();
		}


		private void Init()
		{
			#region Prepare SMO

			DatabaseHost = ConfigurationManager.AppSettings[Const.DatabaseHost];
			DatabaseName = ConfigurationManager.AppSettings[Const.DatabaseName];
			SchemaFrom = ConfigurationManager.AppSettings[Const.SchemaFrom];
			SchemaTo = ConfigurationManager.AppSettings[Const.SchemaTo];
			TablePattern = ConfigurationManager.AppSettings[Const.TablePattern];
			string ConnString = "Data Source=" + DatabaseHost + ";Initial Catalog=" + DatabaseName + ";Integrated Security=SSPI;";

			SmoSqlServer = new Server(new ServerConnection(new SqlConnection(ConnString)));
			Database db = new Database(SmoSqlServer, DatabaseName);
			db.Refresh();

			#endregion

			GetTranslations(db);
		}



		private void GetTranslations(Database db)
		{
			IEnumerable<Table> lstDestinationTables = db.Tables.Cast<Table>().Where(p => p.Schema == SchemaTo && p.Name.StartsWith(TablePattern)).ToList();

			foreach (Table table in lstDestinationTables)
			{
				StringBuilder stringBuilder = new StringBuilder();

				stringBuilder.Append("SELECT" + Environment.NewLine);

				IEnumerable<Column> lstCols = table.Columns.Cast<Column>().Where(p => p.Name != "Id" && p.Name != "InsertDate").ToList();

				foreach (Column column in lstCols)
					stringBuilder.Append(GetDataTypeExpression(column));

				stringBuilder.Remove(stringBuilder.ToString().LastIndexOf(","), 1);

				stringBuilder.Append(" FROM " + SchemaFrom + "." + table.Name);

				string tblExpression = stringBuilder.ToString();

				File.WriteAllText(@"C:\TFS\vroom\FILES\" + "usp_Load_" + table.Name + ".sql", tblExpression, Encoding.ASCII);
			}
		}



		private string GetDataTypeExpression(Column column)
		{
			string columnDataType = column.DataType.ToString();
			StringBuilder dataTypeStringBuilder = new StringBuilder();

			//dataTypeStringBuilder.Append("CAST(");

			switch (column.DataType.SqlDataType)
			{
				case SqlDataType.DateTime2:
					dataTypeStringBuilder.Append("CAST([" + column.Name + "] AS DATETIME2");
					break;
				case SqlDataType.DateTime:
					dataTypeStringBuilder.Append("CAST([" + column.Name + "] AS DATETIME");
					break;
				case SqlDataType.Date:
					dataTypeStringBuilder.Append("CONVERT(DATE, [" + column.Name + "], 101"); //CONVERT(DATE, [BusDate], 101) AS [BusDate],
					break;
				case SqlDataType.Bit:
					dataTypeStringBuilder.Append("CAST([" + column.Name + "] AS BIT");
					break;
				case SqlDataType.BigInt:
					dataTypeStringBuilder.Append("CAST([" + column.Name + "] AS BIGINT");
					break;
				case SqlDataType.Int:
					dataTypeStringBuilder.Append("CAST([" + column.Name + "] AS INT");
					break;
				case SqlDataType.SmallInt:
					dataTypeStringBuilder.Append("CAST([" + column.Name + "] AS SMALLINT");
					break;
				case SqlDataType.TinyInt:
					dataTypeStringBuilder.Append("CAST([" + column.Name + "] AS TINYINT");
					break;
				case SqlDataType.Char:
					dataTypeStringBuilder.Append("CAST([" + column.Name + "] AS CHAR(" + column.DataType.MaximumLength + ")");
					break;
				case SqlDataType.VarChar:
					dataTypeStringBuilder.Append("CAST(" + column.Name.ReplaceDoubleQuotes() + " AS VARCHAR(" + column.DataType.MaximumLength + ")");
					break;
				case SqlDataType.Float:
					dataTypeStringBuilder.Append("CAST(REPLACE([" + column.Name + "], ',', '') AS FLOAT");
					break;
				case SqlDataType.Decimal:
					/*
					 * Seperate number to left and right of decimal point, take from right of decimal point only up to NumericPrecision-NumericScale
					 * Also strip , (1000 seperator)
					 
					dataTypeStringBuilder.Append("( SUBSTRING(REPLACE([" + column.Name + "], ',', ''), 0, CHARINDEX('.', REPLACE([" + column.Name + "], ',', '') , 0)) +" +
												 "  SUBSTRING(REPLACE([" + column.Name + "], ',', ''), CHARINDEX('.', REPLACE([" + column.Name + "], ',', '') , 0)," + (column.DataType.NumericPrecision - column.DataType.NumericScale) + "))" +
												 "  AS DECIMAL(" + column.DataType.NumericPrecision + "," + column.DataType.NumericScale + ")");
					*/
					dataTypeStringBuilder.Append("CAST(CAST([" + column.Name + "] AS FLOAT) " + "AS DECIMAL(" + column.DataType.NumericPrecision + "," + column.DataType.NumericScale + ")");
					break;
				default:
					dataTypeStringBuilder.Append(column.DataType.SqlDataType.ToString());
					break;
			}


			dataTypeStringBuilder.Append(")," + Environment.NewLine);

			return dataTypeStringBuilder.ToString();
		}



	}

}
